package com.koocloud.electroplatemanage.utils;


import com.koocloud.electroplatemanage.pojo.IncomePartUnitVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.lang.reflect.Array;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.koocloud.electroplatemanage.constant.IncomePartJudgeSqlFieldConstant.INCOMEPARTUNIT;

/**
 * @program: Information_platform
 * @description: Excel导入导出工具类
 * @author: xxc
 * @create: 2020-10-26 10:28
 */
@Slf4j
public class ExcelUtil {
    /**
     * excel文件读取数据
     * @param fileName
     * @return
     */
    public static List<List<String>> getData(String fileName){
        List<List<String>> excelData = new ArrayList<>();
        List<String> errorInfo = new ArrayList<>();
        try {
            // 判断选择文件类型是否是Excel格式
            String path = fileName;
            if (!path.endsWith(".xls") && !path.endsWith(".xlsx")) {
                errorInfo.add("请选择Excel格式的文件！");
                excelData.add(errorInfo);
                return excelData;
            }
            Workbook workbook =   WorkbookFactory.create(new File(fileName)); // 自动判断格式
            excelData = getData(workbook);
        } catch (Exception e) {
            e.printStackTrace();
            errorInfo.add("异常："+e.getMessage());
            excelData.add(errorInfo);
            return excelData;
        }
        return excelData;
    }

    /**
     * excel内容转list，支持多个工作页sheet
     * map就是工作页名称与数据
     * List<List<Object>> 就是单页的行列数据
     * @param workbook workbook
     * @return
     */
    private static List<List<String>> getData(Workbook workbook){
        //返回数据
        List<List<String>> sheetData = new ArrayList<>();
        //错误信息
        List<String> errorInfo = new ArrayList<>();
        try{
            //取得excel第一个页签
            Sheet oneSheet = workbook.getSheetAt(0);
            //Sheet oneSheet = workbook.getSheet("Sheet2");//页签对应名称
            //判断第一个页签是否为空
            if (oneSheet == null) {
                errorInfo.add("Excel中没有对应页签,无法完成导入!");
                sheetData.add(errorInfo);
                return sheetData;
            }
            // excel单个页的最大尺寸，行数，列数，防止空行与单元格
            int rows = oneSheet.getLastRowNum(); //最大行数,以0开始
            //页签无数据
            if (rows <= 0){
                errorInfo.add("没有需要导入的数据!");
                sheetData.add(errorInfo);
                return sheetData;
            }

            int cols = 0; //最大列数
            //遍历每行，找出excel此页的最大列数
            for (int i = 0; i < rows + 1; i++) {
                //判断为空，防止空值针
                if (oneSheet.getRow(i) == null){
                    continue;
                }
                //获取当前行的最大列数
                int curCols = oneSheet.getRow(i).getLastCellNum();
                //获得此页excel的最大列数
                cols = Math.max(cols,curCols);
            }
            //遍历
            for (int i = 0; i < rows + 1; i++) {
                //单行数据
                List<String> rowData = new ArrayList<>();
                //获取一行数据信息
                Row oneRow = oneSheet.getRow(i);
                //判断是否为空
                if (oneRow == null){
                    //为空，添加空值
                    sheetData.add(fakeEmptyRow(cols));
                    continue;
                }
                sheetData.add(rowData);
                for (int j = 0; j < cols; j++) {
                    //获取行的每列的值
                    Cell oneCell = oneRow.getCell(j);
                    //如果值为空、或者为空单元格、或者为字符串类型长度为0 则赋值为null
                    if (oneCell == null || oneCell.getCellType().equals(CellType.BLANK)
                            || (oneCell.getCellType().equals(CellType.STRING) && oneCell.getStringCellValue().length() == 0)){
                        rowData.add(null);
                    }else{
                        //赋值
                        rowData.add(getCellValue(oneCell));
                    }
                }

            }
            return sheetData;
        }catch (Exception e ){
            e.printStackTrace();
            errorInfo.add("异常："+e.getMessage());
            sheetData.add(errorInfo);
            return sheetData;
        }
    }

    /**
     *@Description: 根据返回的类型赋值
     *@Param: [oneCell]
     *@return: java.lang.String
     *@Author: xxc
     *@date: 2020/10/28
     */
    private static String getCellValue(Cell oneCell){
        String cellValue = null;

        switch (oneCell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(oneCell)) {
                    Date tempValue = oneCell.getDateCellValue();
                    SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = simpleFormat.format(tempValue);
                }else {
                    cellValue = doubleTrans(oneCell.getNumericCellValue());
                }
                break;
            case STRING:
                cellValue = String.valueOf(oneCell.getStringCellValue());
                break;
            case BOOLEAN:
                cellValue = String.valueOf(oneCell.getBooleanCellValue());
                break;
            case FORMULA:
                try {
                    cellValue = String.valueOf(oneCell.getStringCellValue());
                } catch (IllegalStateException e) {
                    try {
                        cellValue = doubleTrans(oneCell.getNumericCellValue());
                    }catch (IllegalStateException e1){
                        cellValue = null;
                    }
                }
                break;
            case BLANK:
                cellValue = null;
                break;
            case ERROR:
                cellValue = null;
                break;
            default:
                cellValue = null;
                break;
        }
        return cellValue;
    }


    /**
     *@Description: 为空值行的每列添加空值
     *@Param: [cols]
     *@return: java.util.List<java.lang.String>
     *@Author: xxc
     *@date: 2020/10/28
     */
    private static List<String> fakeEmptyRow(int cols){

        List<String> fakeRow = new ArrayList<>();

        for (int i = 0; i < cols; i++) {
            fakeRow.add(null);
        }
        return fakeRow;

    }

    //方法一
    public static String doubleTrans(double num) {
        if(num % 1.0 == 0) {
            return String.valueOf((long)num);
        }
        return String.valueOf(num);
    }

    //double  int
    public static String doubleTrans1(double num) {
        if (Math.round(num) - num == 0) {
            return String.valueOf((long) num);
        }
        return String.valueOf(num);
    }
    //测试导入
    public static void main(String[] args) {
        try {
            List<List<String>> excelData = ExcelUtil.getData("f:/test2.xlsx");
            ArrayList arrayList = (ArrayList) excelData;
            for (Object list:arrayList){
                List<String> list1 = (List) list;
                System.out.println(list1);

            }
            System.out.println(arrayList.size());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

